﻿USE [p301]
GO
PRINT N'Delete  [dbo].[InsertKlient]'
go
/****** Object:  StoredProcedure [dbo].[InsertKlient]    Script Date: 02/18/2008 12:19:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertKlient]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertKlient]



PRINT N'Creating [dbo].[vUksKons]'
GO
CREATE VIEW dbo.vUksKons
AS
SELECT     K_UKS_KONS, K_UKS, K_KPO, K_ZAN, D_KONS, K_KONS, K_RAB, N_PROB, K_OCENKA, N_MERY, N_PRIM, K_KLIENT, K_PROB, K_HELP_ORG, 
                      K_HAR_ZAPROS, K_DALN_RAB, F_HODOTAISTVO, K_ISK, CASE WHEN NOT EXISTS
                          (SELECT     *
                            FROM          b_uks_kons AS t
                            WHERE      t .D_KONS < b_uks_kons.D_KONS AND t .K_UKS = b_uks_kons.K_UKS AND t .K_RAB = b_uks_kons.K_RAB AND 
                                                   t .K_KLIENT = b_uks_kons.K_KLIENT AND t .K_UKS_KONS != b_uks_kons.K_UKS_KONS) AND 
                      K_ZAN != 1 THEN 1 ELSE 0 END AS F_PERV
FROM         dbo.b_uks_kons
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[26] 4[35] 2[17] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "b_uks_kons"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 203
            End
            DisplayFlags = 280
            TopColumn = 15
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 20
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'vUksKons', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', 1, 'SCHEMA', N'dbo', 'VIEW', N'vUksKons', NULL, NULL
GO


PRINT N'Delete  b_uks_kons.F_PERV'
go
BEGIN TRANSACTION
GO
ALTER TABLE p301.dbo.b_uks_kons DROP CONSTRAINT DF__b_uks_kon__F_PER__7167D3BD
GO
ALTER TABLE b_uks_kons DROP COLUMN F_PERV
GO
COMMIT


go
PRINT N'ALTER PROCEDURE dbo.RepSravnitAnalizProvKonsultKvartal'
go
ALTER PROCEDURE dbo.RepSravnitAnalizProvKonsultKvartal
	(
		@K_SRAVNIT_ANALIZ_PROV_KONSULT int, 
		@D_S datetime,	
		@D_PO datetime
	)
AS
-- Отчет «Сравнительный анализ проведенных консультаций»
-- Формирование отчета непосредственно только по кварталам
BEGIN
	
	DECLARE RepSravnitAnalizProvKonsultKvartalCursor cursor LOCAL READ_ONLY FOR 
	-- Запрос выборки данных
	select s_rab.K_RAB, C_PERV, C_POVT, C_INDIV, C_KONS_KPO
	from s_rab
	LEFT JOIN
	(
		-- даннные "Первичная копсультация" Первичная и Повторная консультации
		select K_RAB, Sum(C_PERV) C_PERV, Sum(C_POVT) as C_POVT
		from 
		(
			SELECT 	vUksKons.K_RAB, cast(F_PERV as int) as C_PERV,
					case when Count(K_UKS_KONS) > 0 then Count(K_UKS_KONS) - F_PERV else 0 end C_POVT 	
			FROM vUksKons
			WHERE	vUksKons.D_KONS between @D_S And  @D_PO AND 
					vUksKons.K_ZAN = 2
			GROUP BY vUksKons.K_UKS, vUksKons.K_KLIENT, vUksKons.K_RAB, F_PERV
		) as t
		GROUP BY K_RAB	
	) as tPervPovt
		ON s_rab.K_RAB = tPervPovt.K_RAB
		
	LEFT JOIN
	(
		-- даннные "Индивидуальное занятие" 
		SELECT	vUksKons.K_RAB, Count(K_UKS_KONS) AS C_INDIV
		FROM	vUksKons
		WHERE	vUksKons.D_KONS between @D_S And @D_PO and
				K_ZAN = 3
		GROUP BY vUksKons.K_RAB
	) as tIndiv
		ON s_rab.K_RAB = tIndiv.K_RAB
		
	LEFT JOIN
	(
		-- даннные "Консултации на КПО" (только C_FAKT, C_TEL, C_PISMO, C_INET т.е. K_OBRASCH in (1,2,4,5))
		SELECT 
				b_kpo.K_SPEC AS K_RAB, count(K_KPO) as C_KONS_KPO
		FROM b_kpo
		WHERE	b_kpo.D_OBRASCH between @D_S And @D_PO AND
				b_kpo.F_KONS = 1 AND K_OBRASCH in (1,2,4,5)
		GROUP BY b_kpo.K_SPEC
	) as tKpoKons
		ON s_rab.K_RAB = tKpoKons.K_RAB

	where	not C_PERV is null	or 
			not C_POVT is null	or 
			not C_INDIV is null or 
			not C_KONS_KPO is null

	DECLARE @K_RAB int
	DECLARE @C_PERV int
	DECLARE @C_POVT int 
	DECLARE @C_INDIV int
	DECLARE @C_KONS_KPO int
	
	OPEN RepSravnitAnalizProvKonsultKvartalCursor			
	FETCH NEXT FROM RepSravnitAnalizProvKonsultKvartalCursor into @K_RAB, @C_PERV, @C_POVT, @C_INDIV, @C_KONS_KPO

	DECLARE @C_KVART int
	DECLARE @C_GOD int	
	SET @C_KVART = DATEPART(QUARTER, @D_S)
	SET @C_GOD = DATEPART(YEAR, @D_S)
	
	-- Цикл по данным
	while @@FETCH_STATUS = 0
	BEGIN
        -- Сохраняем даннные "Первичная копсультация" (K_RAZDEL = 1)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_PERV, @K_RAB, 1)

        -- Сохраняем даннные "Повторная копсультация" (K_RAZDEL = 2)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_POVT, @K_RAB, 2)

        -- Сохраняем даннные "Индивидуальное занятие" (K_RAZDEL = 3)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_INDIV, @K_RAB, 3)
		
        -- Сохраняем даннные "Консултации на КПО" (K_RAZDEL = 4)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_KONS_KPO, @K_RAB, 4)		
		
		FETCH NEXT FROM RepSravnitAnalizProvKonsultKvartalCursor into @K_RAB, @C_PERV, @C_POVT, @C_INDIV, @C_KONS_KPO
	END
	
	close RepSravnitAnalizProvKonsultKvartalCursor

RETURN
END



go
PRINT N'ALTER PROCEDURE dbo.RepUchetProvKons'
go
ALTER PROCEDURE dbo.RepUchetProvKons
	(
		@D_S datetime,	
		@D_PO datetime,
		@ERROR_MSG varchar(1024) = '' OUTPUT 	-- строка с текстом ошибки. Если ошибка отсуствовала, то пустая
	)
AS
-- Отчет «Учет проведенных консультаций»
BEGIN

BEGIN TRY
	BEGIN TRAN
	
	DECLARE @K_KONS int
	
	-- Рассчет общий данных
	-- Количество семей, состоящих на учете за отчетный период
	DECLARE @C_SEM int 
	SET @C_SEM = (SELECT count(*) FROM b_uks WHERE b_uks.D_DZ BETWEEN @D_S AND @D_PO)
	-- Количество обратившихся семей за помощью за отчетный период
	DECLARE @C_SEM_KONS int 
	SET @C_SEM_KONS = (SELECT count(distinct K_UKS) FROM vUksKons WHERE vUksKons.D_KONS BETWEEN @D_S AND @D_PO)
	-- Общее количество обратившихся в центр за отчетный период
	DECLARE @C_OBRASCH int 
	SET @C_OBRASCH =(SELECT count(*) FROM b_zhurnal_obrasch WHERE b_zhurnal_obrasch.D_OBRASCH BETWEEN @D_S AND @D_PO)
	
	-- Формируем основную запись
	--
	INSERT INTO bo_kons (D_S, D_PO, C_SEM, C_SEM_KONS, C_OBRASCH) 
	VALUES (@D_S, @D_PO, @C_SEM, @C_SEM_KONS, @C_OBRASCH)

	SELECT @K_KONS = SCOPE_IDENTITY();
	

	
	-- Рассчет всех данных, связь с таблицей s_rab
	INSERT INTO bo_kons_ (K_KONS, K_RAB, 
							C_FAKT, C_FAKT_KRIZIS, C_TEL, C_TEL_KRIZIS, C_PISMO, C_PISMO_KRIZIS, C_INET, C_INET_KRIZIS,
							C_PERV, C_POVT, 
							C_INDIV,
							C_GRUP, C_GRUP_LUDI) 
							
	select @K_KONS, s_rab.K_RAB,  
					C_FAKT, C_FAKT_KRIZIS, C_TEL, C_TEL_KRIZIS, C_PISMO, C_PISMO_KRIZIS, C_INET, C_INET_KRIZIS,
					C_PERV, C_POVT,
					C_INDIV,
					C_GRUP, C_GRUP_LUDI
	from s_rab
	LEFT JOIN
	(
		-- Консультация с КПО
		SELECT b_kpo.K_SPEC AS K_RAB, 
			Sum(case when K_OBRASCH = 1 then 1 else 0 end) AS C_FAKT, 
			Sum(case when F_KRIZIS = 1 and K_OBRASCH = 1 then 1 else 0 end) AS C_FAKT_KRIZIS, 
			Sum(case when K_OBRASCH = 2 then 1 else 0 end) AS C_TEL, 
			Sum(case when F_KRIZIS = 1 And K_OBRASCH = 2 then 1 else 0 end) AS C_TEL_KRIZIS, 
			Sum(case when K_OBRASCH = 4 then 1 else 0 end) AS C_PISMO, 
			Sum(case when F_KRIZIS = 1 And K_OBRASCH = 4 then 1 else 0 end) AS C_PISMO_KRIZIS, 
			Sum(case when K_OBRASCH = 5 then 1 else 0 end) AS C_INET, 
			Sum(case when F_KRIZIS = 1 And K_OBRASCH = 5 then 1 else 0 end) AS C_INET_KRIZIS
		FROM b_kpo
		WHERE	D_OBRASCH between @D_S And @D_PO 
				AND b_kpo.F_KONS = 1
		GROUP BY b_kpo.K_SPEC
	) AS tKonsKpo 
		ON  s_rab.K_RAB = tKonsKpo.K_RAB 
	
	LEFT JOIN
	(
		-- даннные "Первичная копсультация" Первичная и Повторная консультации
		select K_RAB, Sum(C_PERV) C_PERV, Sum(C_POVT) as C_POVT
		from 
		(
			SELECT 	vUksKons.K_RAB, cast(F_PERV as int) as C_PERV,
					case when Count(K_UKS_KONS) > 0 then Count(K_UKS_KONS) - F_PERV else 0 end C_POVT 	
			FROM vUksKons
			WHERE	vUksKons.D_KONS between @D_S And  @D_PO AND 
					vUksKons.K_ZAN = 2
			GROUP BY vUksKons.K_UKS, vUksKons.K_KLIENT, vUksKons.K_RAB, F_PERV
		) as t
		GROUP BY K_RAB	
	) as tPervPovt
		ON s_rab.K_RAB = tPervPovt.K_RAB
		
	LEFT JOIN
	(
		-- даннные "Индивидуальное занятие" 
		SELECT	vUksKons.K_RAB, Count(K_UKS_KONS) AS C_INDIV
		FROM	vUksKons
		WHERE	vUksKons.D_KONS between @D_S And @D_PO and
				K_ZAN = 3
		GROUP BY vUksKons.K_RAB
	) as tIndiv
		ON s_rab.K_RAB = tIndiv.K_RAB
	
	LEFT JOIN
	(
		-- даннные "Групповые занятие" 
		SELECT t_grup.K_RAB, Count(t_grup.K_RAB) AS C_GRUP, Sum(t_grup.C_LUDI) AS C_GRUP_LUDI
		FROM
		(
			SELECT K_RAB, Count(K_UKS_KONS) AS C_LUDI
			FROM vUksKons
			WHERE	D_KONS between @D_S And @D_PO AND
					K_ZAN = 1
			GROUP BY K_RAB, D_KONS
		)
		 as t_grup	
		GROUP BY t_grup.K_RAB
	) as tGrup
		ON s_rab.K_RAB = tGrup.K_RAB
		
	where	not C_FAKT is null or
			not C_FAKT_KRIZIS is null or
			not C_TEL is null or
			not C_TEL_KRIZIS is null or
			not C_PISMO is null or
			not C_PISMO_KRIZIS is null or
			not C_INET is null or
			not C_INET_KRIZIS is null or
			not C_PERV is null	or 
			not C_POVT is null	or 
			not C_INDIV is null or
			not C_GRUP is null or
			not C_GRUP_LUDI is null
		



END TRY
BEGIN CATCH
	
	set @ERROR_MSG = isnull(dbo.GetLastError(),'')
	ROLLBACK TRANSACTION
	RETURN
	
END CATCH;

	COMMIT TRAN
	RETURN
END



go
PRINT N'ALTER TABLE dbo.b_sostav_semyi_kpo DROP COLUMN C_LET'
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.b_sostav_semyi_kpo DROP COLUMN C_LET
GO
COMMIT

go
PRINT N'ALTER TABLE b_sostav_semyi_uks DROP COLUMN C_LET'
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.b_sostav_semyi_uks DROP COLUMN C_LET
GO
COMMIT